package com.woniuxy.mapper;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.FetchType;
import com.woniuxy.pojo.Student;
import com.woniuxy.provider.StudentProvider;

public interface StudentMapper {
	// 分页显示学生信息
	@Select("SELECT * FROM student LIMIT #{0},#{1}")
	@Results({ @Result(id = true, column = "student_id", property = "student_id"),
			@Result(column = "student_name", property = "student_name"), 
			@Result(column = "student_sex", property = "student_sex"),
			@Result(column = "grade_id", property = "grade_Name",

					one = @One(select = "findGradeNameById",

							fetchType = FetchType.EAGER

					)), @Result(column = "house_id", property = "address",

							one = @One(select = "findGradeNameById",

									fetchType = FetchType.EAGER)),
			@Result(column = "user_number", property = "user_number"),
			@Result(column = "student_phone", property = "student_phone")
	})
	public List<Student> ShowAllByPage(int pageNumber, int pageSize);
	
	// 根据班级id查班级名称
		@Select("SELECT grade_Name FROM grade WHERE grade_id = #{id}")
		public String findGradeNameById(int grade_id);

	// 通过房间id查地址
	@Select("SELECT address FROM house WHERE house_id = #{id}")
	public String findAddressById(int house_id);

	// 统计学生总数
	@Select("SELECT COUNT(*) FROM student")
	public int studentCount();

	// 多条件查学生信息
	@SelectProvider(type = StudentProvider.class, method = "select")
	@Results({ @Result(id = true, column = "student_id", property = "student_id"),
			@Result(column = "student_name", property = "student_name"),
			@Result(column = "student_sex", property = "student_sex"),
			@Result(column = "grade_id", property = "grade_Name",

					one = @One(select = "findGradeNameById",

							fetchType = FetchType.EAGER

					)), @Result(column = "house_id", property = "address",

							one = @One(select = "findGradeNameById",

									fetchType = FetchType.EAGER)),
			@Result(column = "user_number", property = "user_number"),
			@Result(column = "student_phone", property = "student_phone") })
	public List<Student> select(Map<String, Object> map);

	// 查询符合条件的学生的数量
	@SelectProvider(type = StudentProvider.class, method = "selectCount")
	public int selectCount(Student student);

	// 通过学生id查询详细信息
	@Select("SELECT * FROM student WHERE student_id = {student_id}")
	@Results({ @Result(id = true, column = "student_id", property = "student_id"),
			@Result(column = "student_name", property = "student_name"),
			@Result(column = "student_sex", property = "student_sex"),
			@Result(column = "grade_id", property = "grade_Name",

					one = @One(select = "findGradeNameById",

							fetchType = FetchType.EAGER

					)),

			@Result(column = "house_id", property = "address",

							one = @One(select = "findGradeNameById",

							fetchType = FetchType.EAGER)),
			@Result(column = "user_number", property = "user_number"),
			@Result(column = "student_phone", property = "student_phone"),
			@Result(column = "house_time", property = "house_time") })
	public Student findStudentById(int student_id);

	// 添加学生
    @Insert("INSERT INTO student VALUES (DEFAULT,#{student_name},#{student_sex},#{grade_id},#{house_id},#{student_phone},#{user_number},#{house_time})")
	public int add(Student student);

	// 查询重复学生
	@Select("SELECT COUNT(*) FROM student WHERE student_name = #{student_name} AND student_phone = #{student_phone}")
	public int StudentIdentical(Student student);

    // 更新学生房间信息
    @Update("UPDATE student SET house_id = #{0} WHERE student_id = #{1}")
    public int updateStudentHouse(int house_id, int student_id);


}
